﻿using System;
using System.Globalization;
using Microsoft.VisualStudio.TestTools.WebTesting;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.ComponentModel;
using QA.TFS.Utils;


namespace QA.TFS.Rules.Validation
{

  [DisplayName("Validate Response againts DataSet")]
  [Description("Validates a page response content againts a entire column of a DataSet.")]
  public class DataSetValidationRule : ValidationRule
  {


    public enum tipoOperacion { TSQL, STOREPROCEDURE };

    // The name of the required tag
    [Description("Column Name required to the validation")]
    private string RequiredLabelValue;
    public string RequiredLabel
    {
      get { return RequiredLabelValue; }
      set { RequiredLabelValue = value; }
    }

    [Description("DataSource Name")]
    private string DataSourceValue;
    public string DataSource
    {
      get { return DataSourceValue; }
      set { DataSourceValue = value; }
    }

    [Description("Catalog Name")]
    private string CatalogValue;
    public string Catalog
    {
      get { return CatalogValue; }
      set { CatalogValue = value; }
    }

    [Description("Operation Type")]
    private string operacion;
    public string Operacion
    {
      get { return operacion; }
      set { operacion = value; }
    }

    [Description("Use Integrated Security")]
    [DefaultValue(true)]
    private bool useIntegratedSecurity;
    public bool UseIntegratedSecurity
    {
      get { return useIntegratedSecurity; }
      set { useIntegratedSecurity = value; }
    }

    [Description("Login User")]
    private string loginUser;
    public string LoginUser
    {
      get { return loginUser; }
      set { loginUser = value; }
    }

    [Description("Password User")]
    private string passwordUser;
    public string PasswordUser
    {
      get { return passwordUser; }
      set { passwordUser = value; }
    }


    // Select * from  HumanResources.Employee Where ManagerID = 21
    [Description("SQL Statement")]
    private string StatementValue;
    public string SQLStatement
    {
      get { return StatementValue; }
      set { StatementValue = value; }
    }

    //Data table object
    private DataTable m_dataTable;

    [DisplayName("Validate Response againts DataSet")]
    [Description("Validates a page response content againts a entire column of a DataSet.")]
    public override void Validate(object sender, ValidationEventArgs e)
    {
      String bodyString = e.Response.BodyString;
      string valuesNotFound = String.Empty;
      string m_ConnectionString = @"Data Source=" + DataSourceValue + ";Initial Catalog=" + CatalogValue + ";";
      if (useIntegratedSecurity == true)
        m_ConnectionString += "Integrated Security=True";
      else
        m_ConnectionString += "User Id=" + loginUser + ";Password=" + passwordUser + ";";

      using (SqlConnection conn = new SqlConnection(m_ConnectionString))
      {
        conn.Open();
        StatementValue = ContextParameterHelper.replaceContextParameters(e.WebTest.Context, StatementValue);
        if (operacion.Equals("TSQL"))
        {
          using (SqlDataAdapter da = new SqlDataAdapter(StatementValue, conn))
          {
            DataTable dtResult = new DataTable();
            dtResult.Locale = CultureInfo.CurrentCulture;

            // Fill the DataTable with the result of the SQL statement
            da.Fill(dtResult);
            m_dataTable = dtResult;
          }
        }
        else if (operacion.Equals("STOREPROCEDURE"))
        {
          using (SqlCommand command = new SqlCommand())
          {
            command.Connection = conn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = StringReplacer.cleanForProcedureCall(StatementValue);
            command.CommandTimeout = 10;
            Dictionary<string, string> values = StringReplacer.extractFromSource(StatementValue);
            foreach (KeyValuePair<string, string> item in values)
              command.Parameters.Add(new SqlParameter(item.Key, item.Value.Trim()));

            using (SqlDataAdapter da = new SqlDataAdapter(command))
            {
              DataTable dtResult = new DataTable();
              dtResult.Locale = CultureInfo.CurrentCulture;

              // Fill the DataTable with the result of the SQL statement
              da.Fill(dtResult);
              m_dataTable = dtResult;
            }
          }
        }
        else
        {
          m_dataTable = null;
          valuesNotFound = null;
        }
      }

      if (m_dataTable != null)
      {
        if (!m_dataTable.Columns.Contains(RequiredLabelValue))
          throw new Exception("Valor buscado no existe en el SQLStatement");

        if (m_dataTable.Rows.Count == 0)
          throw new Exception("Intenta comparar contra una ResultSet sin datos.");

        string[] values = new string[m_dataTable.Rows.Count];
        IEnumerator eRows = m_dataTable.Rows.GetEnumerator();
        //create an object to hold the name value pairs
        Dictionary<String, String> dictionary = new Dictionary<string, string>();
        eRows.Reset();
        int i = 0;
        while (eRows.MoveNext())
        {
          DataRow theRow = (DataRow)eRows.Current;
          if (bodyString.IndexOf(theRow[RequiredLabelValue].ToString(), StringComparison.InvariantCultureIgnoreCase) == -1)
            values[i++] = theRow[RequiredLabelValue].ToString();
        }
        if (i > 0)
          valuesNotFound = String.Join(", ", values);
      }

      bool validated = false;
      if (!string.IsNullOrEmpty(valuesNotFound))
        e.Message = String.Format("No se logro encontrar el(los) valor(es) {0}.", valuesNotFound);
      else
        validated = true;

      e.IsValid = validated;
    }
  }
}
